package edu.uic.ids517;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
//import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.annotation.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.context.FacesContext;
import javax.servlet.http.HttpSession;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

@ManagedBean
@SessionScoped
public class ActionBeanDbaseAccess {
	private DbmsUserBean dbmsUserBean;
	private UserLoginBean userLoginBean;
	private MessageBean messageBean;
	private String userName;
	private String password;
	private String url;
	private boolean status;
	
	private String tableName;
	
	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	private int openCount = 0;

	//	private static final String [] TABLE_TYPES={"TABLE","VIEW"};
	//	private String jdbcDriver;
	private Connection connection;
	private Statement statement;
	private ResultSet resultSet, rs;
	private DatabaseMetaData databaseMetaData;

	private String queryType;
	private String sqlQuery;

	//	private String htmlResultSet;

	private Result result;
	private ArrayList<String> columnNames;
	private int numberColumns;

	private ResultSetMetaData rsmd;
	private int numberRows;

	private boolean renderResult;

	public DbmsUserBean getDbmsUserBean() {
		return dbmsUserBean;
	}

	public void setDbmsUserBean(DbmsUserBean dbmsUserBean) {
		this.dbmsUserBean = dbmsUserBean;
	}

	public ArrayList<String> getColumnNames() {
		return columnNames;
	}

	public void setColumnNames(ArrayList<String> columnNames) {
		this.columnNames = columnNames;
	}

	public int getNumberColumns() {
		return numberColumns;
	}

	public void setNumberColumns(int numberColumns) {
		this.numberColumns = numberColumns;
	}

	public Result getResult() {
		return result;
	}

	public void setResult(Result result) {
		this.result = result;
	}

	public boolean isRenderResult() {
		return renderResult;
	}

	public void setRenderResult(boolean renderResult) {
		this.renderResult = renderResult;
	}

	public boolean isStatus() {
		return status;
	}

	public void setStatus(boolean status) {
		this.status = status;
	}

	public MessageBean getMessageBean() {
		return messageBean;
	}

	public void setMessageBean(MessageBean messageBean) {
		this.messageBean = messageBean;
	}

	public UserLoginBean getUserLoginBean() {
		return userLoginBean;
	}

	public void setUserLoginBean(UserLoginBean userLoginBean) {
		this.userLoginBean = userLoginBean;
	}

	public ResultSet executeSelectSQL(String sqlQuery) {
		this.setSqlQuery(sqlQuery);
		// System.out.println("queryType:" + queryType);

		if(status== false) {
			System.out.println("Not connected to database");
		}

		// SELECT TABLE QUERY
		try {
			resultSet = statement.executeQuery(sqlQuery);

		}
		catch(SQLException e) {
			System.out.println("Error executing SQL Query (SELECT): SQLException information"+ "\n"+sqlQuery);
			System.out.println(e.getMessage());

		}

		return resultSet;
	}

	public boolean executeSQL(String sqlQuery) {
		this.setSqlQuery(sqlQuery);
		// System.out.println("queryType:" + queryType);

		if(status== false) {
			System.out.println("Not connected to database");
			return false;
		}

		// SELECT TABLE QUERY
		if(queryType.equalsIgnoreCase("SELECT")) {
			try {
				resultSet = statement.executeQuery(sqlQuery);
				rs = resultSet;
				return true;
			}
			catch(SQLException e) {
				System.out.println("Error executing SQL Query (SELECT): SQLException information"+ "\n"+sqlQuery);
				System.out.println(messageBean.getMessage());
				return false;
			}
		}

		// CREATE TABLE QUERY
		if(queryType.equalsIgnoreCase("CREATE")) { // not SELECT
			try {
				int resultQuery = statement.executeUpdate(sqlQuery);
				System.out.println("INSIDE CREATE------>"+resultQuery);
				return true;
			}
			catch(SQLException e) {
				//System.out.println("Error executing SQL Query (CREATE): SQLException information"+ "\n"+sqlQuery);
				//System.out.println(e.getMessage());
				messageBean.setErrorMessage(true);
				messageBean.setMessage("A table with that name alredy exists!!");


				return false;
			}

		}

		// INSERT TABLE QUERY
		if(queryType.equalsIgnoreCase("INSERT")) { 
			try {
				int resultQuery = statement.executeUpdate(sqlQuery);
				System.out.println("INSIDE CREATE------>"+resultQuery);
				return true;
			}
			catch(SQLException e) {
				System.out.println("Error executing SQL Query (INSERT): SQLException information"+ "\n"+sqlQuery);
				System.out.println(e.getMessage());
				return false;
			}

		}
		// SELECT
		try {
			resultSet = statement.executeQuery(sqlQuery);
			rs = resultSet;
			// System.out.println("SQL query executed:" + sqlQuery);
			return true;
		}
		catch(SQLException e) {
			System.out.println("Error executing DML Query: SQLException information"+ "\n"+sqlQuery);
			System.out.println(e.getMessage());
			return false;
		}
	}

	public boolean generateResult() {

		renderResult = true;

		try {
			result = ResultSupport.toResult(rs);
			rsmd = rs.getMetaData();
			numberColumns = rsmd.getColumnCount();
			setNumberRows(result.getRowCount());
		}
		catch (SQLException e) {
			System.out.println("SQLException caught in generateResult()");
			// e.printStackTrace();
			renderResult = false;
			return false;
		}
		catch (Exception e) {
			System.out.println("Exception caught in generateResult()");
			// e.printStackTrace();
			renderResult = false;
			return false;
		}
		String columnNameList [] = result.getColumnNames();
		columnNames = new ArrayList<String>(numberColumns);
		for(int i=0; i<numberColumns; i++) {
			columnNames.add(columnNameList[i]);
		}
		return true;
	}

	public boolean openConnection(){
		System.out.print("Connection opened" + openCount++);

		userName= dbmsUserBean.getUserName();
		password = dbmsUserBean.getPassword();
		//dbmsUserBean.getDbms().toLowerCase();		

		//String jdbcDriver = "com.mysql.jdbc.Driver";
		url = "jdbc:mysql://" +  dbmsUserBean.getDbmsHost() +  ":3306/" +
				dbmsUserBean.getDatabaseSchema();

		url = "jdbc:mysql://131.193.209.54:3306/f14g49";
		userName="f14g49";
		password="f14g49QRcdY";

		System.out.println(url + "\n" + userName + "\n" + password);
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection= DriverManager.getConnection(url, userName, password);
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			databaseMetaData = connection.getMetaData();
			messageBean.setErrorMessage(false);
			status=true;

		}catch (ClassNotFoundException e) {
			System.out.println(" Exception Inside Catch 1 : ClassNotFoundException");
			//e.printStackTrace();
			status= false;
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Login Failure");

		}catch(SQLException e) {
			System.out.println(" Exception Inside Catch 2");
			//e.printStackTrace();
			status= false;
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Login Failure");

		}catch(Exception e) {
			System.out.println(" Inside Catch 3 : Exception e");
			//e.printStackTrace();
			status= false;
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Login Failure");
		}

		return status;


	}

	public boolean closeConnection() throws SQLException {
		if(statement != null)
			statement.close();

		if(connection != null)
			connection.close();

		return false;
	}


	public void setQueryType(String type) {
		queryType = type;
		return;

	}

	public int getNumberRows() {
		return numberRows;
	}

	public void setNumberRows(int numberRows) {
		this.numberRows = numberRows;
	}

	public String getSqlQuery() {
		return sqlQuery;
	}

	public void setSqlQuery(String sqlQuery) {
		this.sqlQuery = sqlQuery;
	}

	public String validateUser() throws SQLException{

		openConnection();
		String userType = "";
		String loginQuery;
		String username = userLoginBean.getLoginId();
		String pwd = userLoginBean.getLoginPass();

		if(username.length() == 0 || pwd.length() == 0){
			userType = "invalid";
			return userType;
		}

		ResultSet rs = null; 
		try{
			//loginQuery = "select * from userLoginTable";
			loginQuery = "select userName,password from loginTable where userName = '" + username +"'";
			System.out.println("loginQuery is "+loginQuery);
			statement =  (Statement) connection.createStatement();
			rs = statement.executeQuery(loginQuery);
			System.out.println(rs);
			while(rs.next()){

				insertUserLog();

				if("admin".equalsIgnoreCase(rs.getString(1)) && "admin".equals(rs.getString(2))){
					userType = "admin";
				}
				else userType = "general";
			}
		} catch (SQLException e) {
			messageBean.setErrorMessage(true);
			messageBean.setMessage("User Validation Failed");
			//e.printStackTrace();			
		}
		try {
			if(rs !=null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		closeConnection();
		System.out.println(userType);
		return userType;		
	}

	public boolean registerUser() throws SQLException{
		openConnection();
		System.out.println("inside registerUser");
		boolean status = false;
		String insertQuery = "";
		String fName= userLoginBean.getfName();
		String lName = userLoginBean.getlName();
		String password = userLoginBean.getLoginPass();
		String username = userLoginBean.getLoginId();
		int uin = Integer.parseInt(userLoginBean.getUin());
		String type = "general";

		try{
			insertQuery = "INSERT INTO loginTable(uin, userName, password, type, firstName, lastName)"+
					" "+"VALUES("+uin+" , '"+username+"' , '"+password+"' , '"+type+"' ,'"+fName+"' ,'"+lName+"')";
			System.out.println("createQuery:     "+insertQuery);

			statement = (Statement) connection.createStatement();
			statement.executeUpdate(insertQuery);	

			/*statement = connection.createStatement();
			result = statement.executeUpdate(insertQuery);*/
			System.out.println("Insert successful");

			status = true;		

		} catch (SQLException e) {
			status = false;
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Registration Failure");
			System.out.println("Exception caught for new user register");
			e.printStackTrace();			
		}

		closeConnection();

		return status;
	}

	public String createTable() throws SQLException{
		openConnection();
		
		if("logintable".equals(tableName)){
	
		String  insertQuery = "";
		int uin = 0;
		String userName ="admin";
		String password = "admin";
		String type = "admin";
		String firstName = "Administrator";
		String lastName = "Administrator";
		String createQuery = "create table loginTable(uin integer(11), userName varchar(15),password varchar(15),"
				+ "type varchar(10), firstName varchar(25),lastName varchar(25),primary key(uin));";

		System.out.println("createQuery: " + createQuery);
		try {
			statement =   (Statement) connection.createStatement();
			statement.executeUpdate(createQuery);

			//Add admin details
			insertQuery = "INSERT INTO loginTable(uin, userName,  password, type, firstName, lastName)"+
					" "+"VALUES('"+uin+"' , '"+userName+"' , '"+password+"' , '"+type+"', '"+firstName+"' , '"+lastName+"')";
			statement = (Statement) connection.createStatement();
			int result = statement.executeUpdate(insertQuery);
			closeConnection();
			if(result == 1){
				messageBean.setErrorMessage(true);
				messageBean.setMessage("Login Table Created");
				return "SUCCESS";
			}			
		}
		catch (SQLException e ) {
			System.out.println("Table already exists");
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Login Table Already Exists");
		}
		}
		
		if("logtable".equals(tableName)){
			String createQuery = "create table logTable(logid int auto_increment,userName varchar(15),loginTime timestamp,sessionid varchar(45),primary key(logid))";

			try {
				statement =   (Statement) connection.createStatement();
				statement.executeUpdate(createQuery);
				messageBean.setErrorMessage(true);
				messageBean.setMessage("Log Table Created");
				return "SUCCESS";
			}
			catch (SQLException e ) {
				messageBean.setErrorMessage(true);
				messageBean.setMessage("Log Table Already Exists");
			}
		}
		
		
		
		
		
		return "FAIL";
	}

	public String createUserLogTable() throws SQLException{

		openConnection();

		String createQuery = "create table logTable(logid int auto_increment,userName varchar(15),loginTime timestamp,sessionid varchar(45),primary key(logid))";

		try {
			statement =   (Statement) connection.createStatement();
			statement.executeUpdate(createQuery);
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Log Table Created");
			return "SUCCESS";
		}
		catch (SQLException e ) {
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Log Table Already Exists");
		}

		return "FAIL";

	}

	public String dropTable() throws SQLException {
		openConnection();
		String dropQuery;
		try{
			dropQuery = "drop table " + tableName;
			statement =   (Statement) connection.createStatement();
			statement.executeUpdate(dropQuery);
			closeConnection();
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Table Dropped Successfully");
			return "SUCCESS";
		} catch (SQLException e) {
			messageBean.setErrorMessage(true);
			messageBean.setMessage("Table Doesn't Exist");			
		}
		return "FAIL";
	}

	public void insertUserLog() {

		openConnection();
		String logQuery = "";
		java.util.Date date= new java.util.Date();		
		Timestamp timestamp = new java.sql.Timestamp(date.getTime());

		FacesContext facesContext = FacesContext.getCurrentInstance();
		HttpSession session = (HttpSession) facesContext.getExternalContext().getSession(true);

		try{
			logQuery = "insert into logTable (userName,loginTime,sessionId) values ('" + userLoginBean.getLoginId() + "','" + timestamp + "','" + session.getId() + "')";
			System.out.println("logQuery:     "+logQuery);
			statement =   (Statement) connection.createStatement();
			statement.executeUpdate(logQuery);

		} catch (SQLException e) {
			System.out.println("Exception: insert userLogTable");			
		}
	}

	public String displayTable() throws SQLException, IOException{
				
		openConnection();
		messageBean.setErrorMessage(true);		
		try{			
			statement =  (Statement) connection.createStatement();
			String selectQuery = "select * from " + tableName ;
			System.out.println("display query " + selectQuery );
			rs = statement.executeQuery(selectQuery);

			if(rs.next())
			messageBean.setMessage(write(rs).toString());
			else messageBean.setMessage("Table is Empty");
				
		} catch(SQLException s){
			messageBean.setMessage("Table Doesn't Exist");
		}
		closeConnection();
		return "SUCCESS";
	}

	public StringBuffer write(ResultSet rs) throws IOException, SQLException {

		StringBuffer htmlRows = new StringBuffer();

		try {
			ResultSetMetaData md = rs.getMetaData();
			//System.out.println("md " + md);

			int count = md.getColumnCount();
			htmlRows.append("<table border=1>");

			for (int i=1; i<=count; i++) {
				htmlRows.append("<th>");
				htmlRows.append(md.getColumnLabel(i));
				htmlRows.append("</th>");
			}

			//htmlRows.append("<tr>");

			while (rs.next()) {
				htmlRows.append("<tr>");
				for (int i=1; i<=count; i++) {
					htmlRows.append("<td>");
					htmlRows.append(rs.getString(i));
					htmlRows.append("</td>");
				}
				htmlRows.append("</tr>");
			}
			htmlRows.append("</table>");
		} catch (Exception e) {
			System.out.println("Exception in write method");
			htmlRows = new StringBuffer("Table Doesn't Exist"); 
			return htmlRows;

		}
		System.out.println(htmlRows.toString());
		return htmlRows;
	}
}
